package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_prd_info;

@Repository
public class RetPrdInfoDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    public List<Ret_prd_info> getSheetList(String boxId) {
        String sql = "SELECT * FROM RET_PRD_INFO WHERE BOX_ID_FK = ? ORDER BY convert(SLOT_NO,UNSIGNED)";
        return icimJdbcTemplate.query(sql, new Object[] { boxId }, new RowMapper<Ret_prd_info>() {

            @Override
            public Ret_prd_info mapRow(ResultSet rs, int rowNum) throws SQLException {
                return getEntityFromResult(rs);
            }
        });

    }

    public List<Ret_prd_info> getSheetList(Collection<String> sheetIds) {
        StringBuffer sqlBuffer = new StringBuffer("SELECT * FROM RET_PRD_INFO WHERE PRD_SEQ_ID IN ");
        sqlBuffer.append("  (");

        for (String sheetId : sheetIds) {
            sqlBuffer.append("'").append(sheetId).append("'").append(",");
        }
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append(")");
        return icimJdbcTemplate.query(sqlBuffer.toString(), new RowMapper<Ret_prd_info>() {

            @Override
            public Ret_prd_info mapRow(ResultSet rs, int rowNum) throws SQLException {
                return getEntityFromResult(rs);
            }
        });
    }

    public List<Ret_prd_info> findSheetsByCus(String cusId, Timestamp beginTimestamp, Timestamp endTimestamp) {
        return null;
    }

    public Ret_prd_info getInforById(String prd_seq_id) {
        String sql = "SELECT * FROM RET_PRD_INFO WHERE PRD_SEQ_ID = ?";
        List<Ret_prd_info> infos = icimJdbcTemplate.query(sql, new String[] { prd_seq_id },
                new RowMapper<Ret_prd_info>() {

                    @Override
                    public Ret_prd_info mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return getEntityFromResult(rs);
                    }
                });
        return infos.size() > 0 ? infos.get(0) : new Ret_prd_info();
    }

    private Ret_prd_info getEntityFromResult(ResultSet rs) throws SQLException {
        Ret_prd_info retPrdInfo = new Ret_prd_info();
        retPrdInfo.setPrd_seq_id(rs.getString("PRD_SEQ_ID"));
        retPrdInfo.setGrade(rs.getString("PRD_GRADE"));
        retPrdInfo.setSo_id_fk(rs.getString("SO_ID_FK"));
        retPrdInfo.setGroup_id(rs.getString("GROUP_ID"));
        retPrdInfo.setX_axis_cnt(rs.getInt("X_AXIS_CNT_FK"));
        retPrdInfo.setY_axis_cnt(rs.getInt("Y_AXIS_CNT_FK"));
        retPrdInfo.setMdl_id_fk(rs.getString("MDL_ID_FK"));
        retPrdInfo.setWo_id_fk(rs.getString("WO_ID_FK"));
        retPrdInfo.setShip_box_id_fk(rs.getString("SHIP_BOX_ID_FK"));
        retPrdInfo.setSlot_no(rs.getString("SLOT_NO"));
        retPrdInfo.setBox_id_fk(rs.getString("BOX_ID_FK"));
        retPrdInfo.setAct_stb_timestamp(rs.getTimestamp("ACT_STB_TIMESTAMP"));
        retPrdInfo.setDs_recipe_id(rs.getString("DS_RECIPE_ID"));
        return retPrdInfo;
    }
}
